/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
 */
package BD;

import Objects.Archguid;
import Objects.Archguid_cli;
import Objects.Archguid_cp;
import Objects.Archguid_gfc;
import Objects.Archguip_det;
import Objects.Archguip_pro_cg;
import Objects.Archguip_pro_dv;
import Objects.Archguip_sum;
import Objects.City;
import Objects.Country;
import Objects.GroupSupplier;
import Objects.ItemLeftBar;
import Objects.ItemPrintScreen;
import Objects.Measure;
import Objects.Municipality;
import Objects.Parish;
import Objects.Reason;
import Objects.Rol;
import Objects.Sesion;
import Objects.Sex;
import Objects.State;
import Objects.Supplier;
import Objects.Unit;
import Objects.Usuario;
import Objects.log_CGuias;
import Objects.log_CGuias_falt_cg;
import Objects.log_CGuias_falt_dv;
import Objects.log_CGuias_perm;
import Objects.log_Personal;
import Objects.log_TMarca;
import Objects.log_TPersonal;
import Objects.log_TProced;
import Objects.log_TTransp;
import Objects.log_Vehiculos;
import Tools.Datos;
import Tools.Tools;
import java.sql.*;
import java.time.LocalDate;
import java.util.Vector;

/**
 * 
 * @author ARMGARCES
 */
public class Bd {
    protected String[] params;
    protected Connection connection;
    
    /***************************************************************************/
    /********************************** BD *************************************/
    /***************************************************************************/

    /**
     * 
     */
    public Bd(){
       
    }
    /**
     * 
     * @return 
     */
    public Connection open(){
        return null;
    } 

    /***************************************************************************/
    /******************************** SESION ***********************************/
    /***************************************************************************/

    /**
     * 
     * @param usr
     * @param pwd
     * @return
     * @throws Exception 
     */
    public Sesion iniciarSesion(String usr, String pwd) throws Exception{
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        StringBuilder sqlSp = new StringBuilder();
        sqlSp.append("{call sp_get_user_sesion(?, ?)}");
            
        if (connection != null){
            try {
                CallableStatement cstmt = connection.prepareCall(sqlSp.toString());
                cstmt.setString("@id_user", usr);
                cstmt.setString("@pswd", pwd);   
                
                ResultSet rs = cstmt.executeQuery();                
                
                if(rs.next() && rs.getObject(1) != null){                            
                    Sesion sesion = new Sesion(rs);
                    notificarSesion("Lin", usr, "Inicio Sesion");
                    return sesion;                                                                
                }                
            } catch (Exception e) {
                Tools.getErrorMessage(e.getStackTrace(), e.getMessage());
                throw e;
            }finally{
                connection.close();
            }
        }else {
            System.out.println("Error: Connexion no activa");
        }
        
        return null;
    }
    /**
     * 
     * @throws Exception 
     */
    public void cerrarSesion() throws Exception{
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        if (connection != null){
            try{
                notificarSesion("Lof", "", "Cerrado Forzado de Sesion");                                 
            } catch (Exception e) {
                Tools.getErrorMessage(e.getStackTrace(), e.getMessage());
                throw e;
            }finally{
                connection.close();
            }
        }else{
            System.out.println("Error: Connexion no activa");
        }               
    }
    /**
     * 
     * @param usr
     * @throws Exception 
     */
    public void cerrarSesion(Sesion usr) throws Exception{
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        if (connection != null){
            try{
                notificarSesion("Lot",usr.getUsername(),"Cerrar Sesion");                                 
            } catch (Exception e) {
                Tools.getErrorMessage(e.getStackTrace(), e.getMessage());
                throw e;
            }finally{
                connection.close();
            }
        }else{
            System.out.println("Error: Connexion no activa");
        }               
    }    
    /**
     * 
     * @param tipo
     * @param usr
     * @param mnsj 
     */
    private void notificarSesion(String tipo,String usr,String mnsj) {
        try{
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_ins_user_sesion(?,?,?,?,?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@tipo_aud", tipo);
               cstmt.setString("@usuario", usr);
               String[] data = Datos.getLocalHost();
               cstmt.setString("@ip_terminal",  data[0]);
               cstmt.setString("@ip_direccion", data[1]);
               cstmt.setString("@ip_mac",       data[2]);
               cstmt.setString("@mensaje", mnsj);
               cstmt.execute();             
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(Exception e){
           StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }
    }


    /***************************************************************************/
    /******************************** AUDIT ************************************/
    /***************************************************************************/

    /**
     * 
     * @param pkValue
     * @param mensaje
     * @throws SQLException 
     */
    private void auditar(String pkValue,String mensaje) throws SQLException{
        StringBuilder sqlAud = new StringBuilder();
        sqlAud.append("{call sp_upd_user_audit(?,?,?,?,?,?,?,?)}");
        CallableStatement cstmt1 = connection.prepareCall(sqlAud.toString());                           
        cstmt1.setString("@audit_tb", "tb_audit_bd");
        cstmt1.setString("@audit_pk", "id_aud_bd");
        cstmt1.setString("@pk_value", pkValue);
        cstmt1.setString("@id_user", Datos.getSesion().getUsername());

        String[] host = Datos.getLocalHost();
        cstmt1.setString("@ip_terminal", host[0]);
        cstmt1.setString("@ip_direccion",host[1]);
        cstmt1.setString("@ip_mac", host[2]);

        cstmt1.setString("@mensaje", mensaje);
        cstmt1.execute();
    }

    
    /***************************************************************************/
    /******************************** TOOLBAR **********************************/
    /***************************************************************************/
    
    /**
     * 
     * @return 
     * @throws java.sql.SQLException 
     */
    public Integer[] load_ToolBar() throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_menu_top( ?, ?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                
               cstmt.setString("@id_user"  , Datos.getSesion().getUsername() );
               cstmt.setInt("@id_screen"  , Datos.getIdScreen()  );
               ResultSet rs = cstmt.executeQuery(); 
               if(rs.next()){                          
                  Integer[] result = new Integer[14];
                  for (int i = 0; i < result.length; i++) {
                    result[i] = rs.getInt(i+1);
                  }
                  return result;
               }    
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){  
            e.printStackTrace();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /******************************** LEFTBAR **********************************/
    /***************************************************************************/
    
    /**
     * 
     * @return
     * @throws SQLException 
     */
    public ItemLeftBar[] load_Menu() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_menu_left(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@id_user", Datos.getSesion().getUsername());
               
               ResultSet result = cstmt.executeQuery();            
               Vector<ItemLeftBar> vector = new Vector<>();
               
               while(result.next()) {
                   ItemLeftBar menu = new ItemLeftBar(result);
                   vector.add(menu);
               }
               
               ItemLeftBar[] menus = new ItemLeftBar[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   menus[i] = vector.elementAt(i);                    
               }
               return menus;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){          
            e.printStackTrace();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /********************************* USERS ***********************************/
    /***************************************************************************/
    
    /**
     * 
     * @return 
     * @throws java.sql.SQLException 
     */
    public Usuario[] load_Users() throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_user_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Usuario> vector = new Vector<>();
               
               while(result.next()) {
                   Usuario usuario = new Usuario(result);
                   vector.add(usuario);
               }
               
               Usuario[] users = new Usuario[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   users[i] = vector.elementAt(i);                    
               }
               
               return users;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){              
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * Valida si el username generado ya se encuentra asignado a un usuario en la BD
     * @param username valor a ser chequeado en la BD
     * @return true si el username esta en uso, false si esta disponible el username
     * @throws java.sql.SQLException
     */
    public boolean check_UserName(String username) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_user_check(?,?)}");
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@id_user", username);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * 
     * @param usuario
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_UserStatus(Usuario usuario) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_user_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setString("@id_user", usuario.getUsername());
            int value = 0;
            if(usuario.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(usuario.getUsername(),"deleteUser:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE, 3 UPDATE PSWD
     * @param usuario
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_User(int operacion, Usuario usuario) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_user_data(?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Nuevo Usuario:";
                    break;
                case 2:
                    sql.append("{call sp_upd_user_basic(?, ?, ?, ?, ?, ?)}");
                    mensj = "Actualizando Usuario:";
                    break;
                case 3:
                    sql.append("{call sp_upd_user_pswd(?, ?)}");
                    mensj = "Actualizando Clave de Usuario:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@id_user"      , usuario.getUsername() );
                    cstmt.setString("@nombre1"      , usuario.getNombre1().toUpperCase());
                    if(usuario.getNombre2() == null) 
                        cstmt.setString("@nombre2", null);
                    else
                        cstmt.setString("@nombre2", usuario.getNombre2().toUpperCase());
                    cstmt.setString("@apellido1"    , usuario.getApellido1().toUpperCase());
                    if(usuario.getApellido2() == null) 
                        cstmt.setString("@apellido2", null);
                    else
                        cstmt.setString("@apellido2", usuario.getApellido2().toUpperCase());
                    cstmt.setString("@clave"        , usuario.getPswd());
                    cstmt.setInt("@id_rol"          , usuario.getRol().getIdRol());
                    break;
                case 2:
                    cstmt.setString("@id_user"      , usuario.getUsername() );
                    cstmt.setString("@nombre1"      , usuario.getNombre1().toUpperCase());
                    if(usuario.getNombre2() != null) 
                        cstmt.setString("@nombre2", usuario.getNombre2().toUpperCase());
                    cstmt.setString("@apellido1"    , usuario.getApellido1().toUpperCase());
                    if(usuario.getApellido2() != null) 
                        cstmt.setString("@apellido2", usuario.getApellido2().toUpperCase());
                    cstmt.setInt("@id_rol"          , usuario.getRol().getIdRol());
                    break;
                case 3:
                    cstmt.setString("@id_user"      , usuario.getUsername() );
                    cstmt.setString("@clave"        , usuario.getPswd());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(usuario.getUsername(),mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            e.printStackTrace();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Usuario[] find_User(String find) throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_user_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Usuario> vector = new Vector<>();
               
               while(result.next()) {
                   Usuario usuario = new Usuario(result);
                   vector.add(usuario);
               }
               
               Usuario[] users = new Usuario[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   users[i] = vector.elementAt(i);                    
               }
               
               return users;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    /***************************************************************************/
    /********************************* USERS ***********************************/
    /***************************************************************************/
    
    /**
     * 
     * @return 
     * @throws java.sql.SQLException 
     */
    public Rol[] load_Rols() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_role_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Rol> vector = new Vector<>();
               
               while(result.next()) {
                   Rol rol = new Rol(result);
                   vector.add(rol);
               }
               
               Rol[] rols = new Rol[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   rols[i] = vector.elementAt(i);                    
               }
               return rols;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /********************************* ROLE ************************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Rol[] load_Rol() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_role_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Rol> vector = new Vector<>();
               
               while(result.next()) {
                   Rol role = new Rol(result);
                   vector.add(role);
               }
               
               Rol[] role = new Rol[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   role[i] = vector.elementAt(i);                    
               }
               return role;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Rol[] find_Rol(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_role_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Rol> vector = new Vector<>();
               
               while(result.next()) {
                   Rol role = new Rol(result);
                   vector.add(role);
               }
               
               Rol[] role = new Rol[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   role[i] = vector.elementAt(i);                    
               }
               
               return role;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param role
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_Rol(int operacion, Rol role) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_role(?, ?)}");
                    mensj = "Nuevo Rol:";
                    break;
                case 2:
                    sql.append("{call sp_upd_role_basic(?, ?, ?)}");
                    mensj = "Actualizando Rol:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , role.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , role.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@id_role"         , role.getIdRol());
                    cstmt.setString("@nombre"       , role.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , role.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(role.getIdRol()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param rolename valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_Rol(String rolename) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_role_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@role", rolename);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param role
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_Rol(Rol role) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_role_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_role", role.getIdRol());
            int value = 0;
            if(role.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(role.getIdRol()+ "","deleteUnit:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }

    
    /***************************************************************************/
    /***************************** GROUPSUPPLIER *******************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    
    public GroupSupplier[] load_GroupSupplier() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_group_supplier_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<GroupSupplier> vector = new Vector<>();
               
               while(result.next()) {
                   GroupSupplier groupsupplier = new GroupSupplier(result);
                   vector.add(groupsupplier);
               }
               
               GroupSupplier[] groupsupplier = new GroupSupplier[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   groupsupplier[i] = vector.elementAt(i);                    
               }
               return groupsupplier;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public GroupSupplier[] find_GroupSupplier(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_group_supplier_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@grp_supplier"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<GroupSupplier> vector = new Vector<>();
               
               while(result.next()) {
                   GroupSupplier groupsupplier = new GroupSupplier(result);
                   vector.add(groupsupplier);
               }
               
               GroupSupplier[] groupsupplier = new GroupSupplier[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   groupsupplier[i] = vector.elementAt(i);                    
               }
               
               return groupsupplier;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param groupsupplier
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_GroupSupplier(int operacion, GroupSupplier groupsupplier) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_group_supplier(?, ?)}");
                    mensj = "Nuevo Grupo de Proveedor:";
                    break;
                case 2:
                    sql.append("{call sp_upd_group_supplier_basic(?, ?, ?)}");
                    mensj = "Actualizando Grupo de Proveedor:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , groupsupplier.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , groupsupplier.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@grp_supplier"    , groupsupplier.getIdGroupSupplier() );
                    cstmt.setString("@nombre"       , groupsupplier.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , groupsupplier.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(groupsupplier.getIdGroupSupplier() + "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            e.printStackTrace();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param groupsuppliername valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_GroupSupplier(String groupsuppliername) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_group_supplier_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@grp_supplier", groupsuppliername);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param groupsupplier
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_GroupSupplier(GroupSupplier groupsupplier) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_group_supplier_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@grp_supplier", groupsupplier.getIdGroupSupplier());
            int value = 0;
            if(groupsupplier.getStatus()== 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(groupsupplier.getIdGroupSupplier() + "","deleteGroupSupplier:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }

    
    /***************************************************************************/
    /******************************** COUNTRY **********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Country[] load_Country() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_country_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Country> vector = new Vector<>();
               
               while(result.next()) {
                   Country country = new Country(result);
                   vector.add(country);
               }
               
               Country[] country = new Country[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   country[i] = vector.elementAt(i);                    
               }
               return country;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Country[] find_Country(String find) throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_country_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@country"  , find );
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Country> vector = new Vector<>();
               
               while(result.next()) {
                   Country country = new Country(result);
                   vector.add(country);
               }
               
               Country[] country = new Country[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   country[i] = vector.elementAt(i);                    
               }
               
               return country;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /********************************* STATE ***********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public State[] load_State() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_state_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<State> vector = new Vector<>();
               
               while(result.next()) {
                   State state = new State(result);
                   vector.add(state);
               }
               
               State[] state = new State[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   state[i] = vector.elementAt(i);                    
               }
               return state;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public State[] find_State(String find) throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_state_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@country"  , find );
               ResultSet result = cstmt.executeQuery();            
               
               Vector<State> vector = new Vector<>();
               
               while(result.next()) {
                   State state = new State(result);
                   vector.add(state);
               }
               
               State[] state = new State[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   state[i] = vector.elementAt(i);                    
               }
               
               return state;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /********************************** CITY ***********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public City[] load_City() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_city_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<City> vector = new Vector<>();
               
               while(result.next()) {
                   City city = new City(result);
                   vector.add(city);
               }
               
               City[] city = new City[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   city[i] = vector.elementAt(i);                    
               }
               return city;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public City[] find_City(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_city_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@state"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<City> vector = new Vector<>();
               
               while(result.next()) {
                   City city = new City(result);
                   vector.add(city);
               }
               
               City[] city = new City[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   city[i] = vector.elementAt(i);                    
               }
               
               return city;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /****************************** MUNICIPALITY *******************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Municipality[] load_Municipality() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_municipality_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Municipality> vector = new Vector<>();
               
               while(result.next()) {
                   Municipality municipality = new Municipality(result);
                   vector.add(municipality);
               }
               
               Municipality[] municipality = new Municipality[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   municipality[i] = vector.elementAt(i);                    
               }
               return municipality;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Municipality[] find_Municipality(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_municipality_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@municipality"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Municipality> vector = new Vector<>();
               
               while(result.next()) {
                   Municipality municipality = new Municipality(result);
                   vector.add(municipality);
               }
               
               Municipality[] municipality = new Municipality[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   municipality[i] = vector.elementAt(i);                    
               }
               
               return municipality;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /********************************* PARISH **********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Parish[] load_Parish() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_parish_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Parish> vector = new Vector<>();
               
               while(result.next()) {
                   Parish parish = new Parish(result);
                   vector.add(parish);
               }
               
               Parish[] parish = new Parish[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   parish[i] = vector.elementAt(i);                    
               }
               return parish;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param findM
     * @param findS
     * @return 
     * @throws java.sql.SQLException 
     */
    public Parish[] find_Parish(String findM, String findS) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_parish_find(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@municipality"  , findM );
               cstmt.setString("@state"  , findS );
               ResultSet result = cstmt.executeQuery();            
               Vector<Parish> vector = new Vector<>();
               
               while(result.next()) {
                   Parish parish = new Parish(result);
                   vector.add(parish);
               }
               
               Parish[] parish = new Parish[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   parish[i] = vector.elementAt(i);                    
               }
               
               return parish;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /******************************** MEASURE **********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Measure[] load_Measure() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_measure_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Measure> vector = new Vector<>();
               
               while(result.next()) {
                   Measure measure = new Measure(result);
                   vector.add(measure);
               }
               
               Measure[] measure = new Measure[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   measure[i] = vector.elementAt(i);                    
               }
               return measure;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Measure[] find_Measure(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_measure_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Measure> vector = new Vector<>();
               
               while(result.next()) {
                   Measure measure = new Measure(result);
                   vector.add(measure);
               }
               
               Measure[] measure = new Measure[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   measure[i] = vector.elementAt(i);                    
               }
               
               return measure;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param measure
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_Measure(int operacion, Measure measure) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_measure(?, ?)}");
                    mensj = "Nueva Medida:";
                    break;
                case 2:
                    sql.append("{call sp_upd_measure_basic(?, ?, ?)}");
                    mensj = "Actualizando Medida:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , measure.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , measure.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@id_measure"      , measure.getIdMeasure());
                    cstmt.setString("@nombre"       , measure.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , measure.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(measure.getIdMeasure() + "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param measurename valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_Measure(String measurename) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_measure_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@measure", measurename);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param measure
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_Measure(Measure measure) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_measure_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_measure", measure.getIdMeasure());
            int value = 0;
            if(measure.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(measure.getIdMeasure() + "","deleteMeasure:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }


    /***************************************************************************/
    /********************************* UNIT ************************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Unit[] load_Unit() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_unit_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Unit> vector = new Vector<>();
               
               while(result.next()) {
                   Unit unit = new Unit(result);
                   vector.add(unit);
               }
               
               Unit[] unit = new Unit[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   unit[i] = vector.elementAt(i);                    
               }
               return unit;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Unit[] find_Unit(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_unit_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Unit> vector = new Vector<>();
               
               while(result.next()) {
                   Unit unit = new Unit(result);
                   vector.add(unit);
               }
               
               Unit[] unit = new Unit[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   unit[i] = vector.elementAt(i);                    
               }
               
               return unit;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param unit
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_Unit(int operacion, Unit unit) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_unit(?, ?)}");
                    mensj = "Nueva Unidad:";
                    break;
                case 2:
                    sql.append("{call sp_upd_unit_basic(?, ?, ?)}");
                    mensj = "Actualizando Unidad:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , unit.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , unit.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@id_unit"         , unit.getIdUnit());
                    cstmt.setString("@nombre"       , unit.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , unit.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(unit.getIdUnit()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param unitname valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_Unit(String unitname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_unit_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@unit", unitname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param unit
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_Unit(Unit unit) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_unit_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_unit", unit.getIdUnit());
            int value = 0;
            if(unit.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(unit.getIdUnit() + "","deleteUnit:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }

    
    /***************************************************************************/
    /******************************** REASON ***********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Reason[] load_Reason() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_reason_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Reason> vector = new Vector<>();
               
               while(result.next()) {
                   Reason reason = new Reason(result);
                   vector.add(reason);
               }
               
               Reason[] reason = new Reason[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   reason[i] = vector.elementAt(i);                    
               }
               return reason;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Reason[] find_Reason(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_reason_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Reason> vector = new Vector<>();
               
               while(result.next()) {
                   Reason reason = new Reason(result);
                   vector.add(reason);
               }
               
               Reason[] measure = new Reason[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   measure[i] = vector.elementAt(i);                    
               }
               
               return measure;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param reason
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_Reason(int operacion, Reason reason) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_reason(?, ?, ?)}");
                    mensj = "Nuevo Motivo:";
                    break;
                case 2:
                    sql.append("{call sp_upd_reason_basic(?, ?, ?, ?)}");
                    mensj = "Actualizando Motivo:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , reason.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , reason.getAbrev().toUpperCase());
                    cstmt.setInt("@val"             , reason.getValDev());
                    break;
                case 2:
                    cstmt.setInt("@id_motivo"       , reason.getIdReason());
                    cstmt.setString("@nombre"       , reason.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , reason.getAbrev().toUpperCase());
                    cstmt.setInt("@val"             , reason.getValDev());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(reason.getIdReason()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param reasonname valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_Reason(String reasonname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_reason_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@motivo", reasonname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param reason
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_Reason(Reason reason) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_reason_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_motivo", reason.getIdReason());
            int value = 0;
            if(reason.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(reason.getIdReason()+ "","deleteReason:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }


    /***************************************************************************/
    /******************************** SUPPLIER *********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Supplier[] load_Supplier() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_supplier_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Supplier> vector = new Vector<>();
               
               while(result.next()) {                   
                   Supplier supplier = new Supplier(result);
                   vector.add(supplier);
               }
               
               Supplier[] supplier = new Supplier[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   supplier[i] = vector.elementAt(i);                    
               }
               return supplier;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Supplier[] find_Supplier(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_supplier_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Supplier> vector = new Vector<>();
               
               while(result.next()) {
                   Supplier supplier = new Supplier(result);
                   vector.add(supplier);
               }
               
               Supplier[] supplier = new Supplier[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   supplier[i] = vector.elementAt(i);                    
               }
               
               return supplier;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param supplier
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_Supplier(int operacion, Supplier supplier) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_prov_data(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Nuevo Proveedor:";
                    break;
                case 2:
                    sql.append("{call sp_upd_prov_basic(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Actualizando Proveedor:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            cstmt.setString("@rif_prov"         , supplier.getRif().toUpperCase()  );
            cstmt.setInt("@rif_val"             , supplier.getRif_val());
            cstmt.setInt("@grp_prov"            , supplier.getGroupsupplier().getIdGroupSupplier());
            cstmt.setString("@nombre"           , supplier.getNombre().toUpperCase());
            cstmt.setString("@rlegal"           , supplier.getRlegal());
            cstmt.setInt("@id_ciudad"           , supplier.getCity().getIdPoblacion());
            cstmt.setInt("@id_parroquia"        , supplier.getParish().getIdPoblacion());
            cstmt.setInt("@id_municipio"        , supplier.getMunicipality().getIdPoblacion());
            cstmt.setInt("@id_estado"           , supplier.getState().getIdPoblacion());
            cstmt.setInt("@id_pais"             , supplier.getCountry().getIdPoblacion());
            cstmt.setString("@direccion"        , supplier.getDireccion().toLowerCase());
            cstmt.setString("@telefonos"        , supplier.getTelefonos());
            cstmt.setString("@fax"              , supplier.getFax());
            cstmt.setString("@correo"           , supplier.getCorreo().toLowerCase());
            cstmt.setString("@contacto"         , supplier.getContacto());
            cstmt.setString("@celular"          , supplier.getCelular());

            cstmt.execute();            
            // Auditar el proceso
            auditar(supplier.getIdSupplier() + "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param supplierRif valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_Supplier(String supplierRif) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_supplier_check(?,?,?)}");
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@rif_prov", supplierRif);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.registerOutParameter("seniat", java.sql.Types.INTEGER);               
               cstmt.execute();
               Datos.setStSeniat(cstmt.getInt("seniat"));
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param supplier
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_Supplier(Supplier supplier) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_prov_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setString("@rif_prov", supplier.getRif());
            int value = 0;
            if(supplier.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(supplier.getIdSupplier() + "","deleteProveedor:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    
   
    /***************************************************************************/
    /********************************** SEX ************************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public Sex[] load_Sex() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_sex_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<Sex> vector = new Vector<>();
               
               while(result.next()) {
                   Sex sex = new Sex(result);
                   vector.add(sex);
               }
               
               Sex[] sex = new Sex[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   sex[i] = vector.elementAt(i);                    
               }
               return sex;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Sex[] find_Sex(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_sex_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Sex> vector = new Vector<>();
               
               while(result.next()) {
                   Sex sex = new Sex(result);
                   vector.add(sex);
               }
               
               Sex[] sex = new Sex[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   sex[i] = vector.elementAt(i);                    
               }
               
               return sex;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param sex
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_Sex(int operacion, Sex sex) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_sex(?, ?)}");
                    mensj = "Nuevo Sexo:";
                    break;
                case 2:
                    sql.append("{call sp_upd_sex_basic(?, ?, ?)}");
                    mensj = "Actualizando Sexo:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , sex.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , sex.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@grp_supplier"    , sex.getIdSexo());
                    cstmt.setString("@nombre"       , sex.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , sex.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(sex.getIdSexo()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param sexname valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_Sex(String sexname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_sex_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@id_sex", sexname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param sex
     * @return 
     * @throws java.lang.Exception
    */
    public boolean change_Sex(Sex sex) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_sex_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_sex", sex.getIdSexo());
            int value = 0;
            if(sex.getStatus()== 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(sex.getIdSexo()+ "","deleteSex:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }

    
    /***************************************************************************/
    /****************************** LOG_PERSONAL *******************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_Personal[] load_log_Personal() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_personal_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            

               Vector<log_Personal> vector = new Vector<>();
               
               while(result.next()) {                   
                   log_Personal log_personal = new log_Personal(result);
                   vector.add(log_personal);
               }
               
               log_Personal[] log_personal = new log_Personal[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_personal[i] = vector.elementAt(i);                    
               }
               return log_personal;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_Personal[] find_log_Personal(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_personal_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_Personal> vector = new Vector<>();
               
               while(result.next()) {
                   log_Personal log_personal = new log_Personal(result);
                   vector.add(log_personal);
               }
               
               log_Personal[] log_personal = new log_Personal[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_personal[i] = vector.elementAt(i);                    
               }
               
               return log_personal;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @param tpersonal1
     * @param tpersonal2
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_Personal[] find_log_Personal_tp(String find, int tpersonal1, int tpersonal2) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_personal_find_tp(?,?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               cstmt.setInt("@tpersonal1"  , tpersonal1 );
               cstmt.setInt("@tpersonal2"  , tpersonal2 );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_Personal> vector = new Vector<>();
               
               while(result.next()) {
                   log_Personal log_personal = new log_Personal(result);
                   vector.add(log_personal);
               }
               
               log_Personal[] log_personal = new log_Personal[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_personal[i] = vector.elementAt(i);                    
               }
               
               return log_personal;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_personal
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_Personal(int operacion, log_Personal log_personal) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_log_personal_data(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Nuevo Personal:";
                    break;
                case 2:
                    sql.append("{call sp_upd_log_personal_basic(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Actualizando Personal:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            cstmt.setString("@nro_ci"           , log_personal.getNro_ci());
            cstmt.setInt("@tpersonal"           , log_personal.getTpersonal().getIdTPersonal());
            cstmt.setInt("@sexo"                , log_personal.getSex().getIdSexo());
            cstmt.setString("@nombres"          , log_personal.getNombres().toUpperCase());
            cstmt.setString("@apellidos"        , log_personal.getApellidos().toUpperCase());
            cstmt.setInt("@id_ciudad"           , log_personal.getCity().getIdPoblacion());
            cstmt.setInt("@id_parroquia"        , log_personal.getParish().getIdPoblacion());
            cstmt.setInt("@id_municipio"        , log_personal.getMunicipality().getIdPoblacion());
            cstmt.setInt("@id_estado"           , log_personal.getState().getIdPoblacion());
            cstmt.setInt("@id_pais"             , log_personal.getCountry());
            cstmt.setString("@direccion"        , log_personal.getDireccion().toLowerCase());
            cstmt.setString("@telefonos"        , log_personal.getTelefonos());
            cstmt.setString("@celular"          , log_personal.getCelular());
            if (log_personal.getCorreo() != null)
                cstmt.setString("@correo"       , log_personal.getCorreo().toLowerCase());
            else
                cstmt.setString("@correo"       , log_personal.getCorreo());

            if (log_personal.getRuta_ci() != null)
                cstmt.setString("@ruta_ci"          , log_personal.getRuta_ci().toLowerCase());
            else
                cstmt.setString("@ruta_ci"          , log_personal.getRuta_ci());
                
            if (log_personal.getRuta_lc() != null)
                cstmt.setString("@ruta_lc"          , log_personal.getRuta_lc().toLowerCase());
            else
                cstmt.setString("@ruta_lc"          , log_personal.getRuta_lc());
                
            if (log_personal.getRuta_cm() != null)
                cstmt.setString("@ruta_cm"          , log_personal.getRuta_cm().toLowerCase());
            else
                cstmt.setString("@ruta_cm"          , log_personal.getRuta_cm());
                
            if (log_personal.getRuta_cs() != null)
                cstmt.setString("@ruta_cs"          , log_personal.getRuta_cs().toLowerCase());
            else
                cstmt.setString("@ruta_cs"          , log_personal.getRuta_cs());

            if (log_personal.getRuta_ma() != null)
                cstmt.setString("@ruta_ma"          , log_personal.getRuta_ma().toLowerCase());
            else
                cstmt.setString("@ruta_ma"          , log_personal.getRuta_ma());
            
            if(log_personal.getFec_lc().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_lc"         , null);
            else
                cstmt.setDate("@fec_lc"         , log_personal.getFec_lc());

            if(log_personal.getFec_cm().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_cm"         , null);
            else
                cstmt.setDate("@fec_cm"         , log_personal.getFec_cm());

            if(log_personal.getFec_cs().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_cs"         , null);
            else
                cstmt.setDate("@fec_cs"         , log_personal.getFec_cs());

            if(log_personal.getFec_ma().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_ma"         , null);
            else
                cstmt.setDate("@fec_ma"         , log_personal.getFec_ma());
                
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_personal.getNro_ci()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param log_PersonalCi valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_log_Personal(String log_PersonalCi) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_personal_check(?,?)}");
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@log_Personal", log_PersonalCi);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param log_personal
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_log_Personal(log_Personal log_personal) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_log_personal_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setString("@nro_ci", log_personal.getNro_ci());
            int value = 0;
            if(log_personal.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_personal.getIdPersonal()+ "","deletelog_Personal:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param param1
     * @param param2
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_Personal[] print_log_Personal(String param1, String param2) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_personal_print(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@param1"  , param1 );
               cstmt.setString("@param2"  , param2 );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_Personal> vector = new Vector<>();
               
               while(result.next()) {
                   log_Personal log_personal = new log_Personal(result);
                   vector.add(log_personal);
               }
               
               log_Personal[] log_personal = new log_Personal[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_personal[i] = vector.elementAt(i);                    
               }
               
               return log_personal;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    
   
    /***************************************************************************/
    /***************************** log_TPersonal *******************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TPersonal[] load_log_TPersonal() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tpersonal_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<log_TPersonal> vector = new Vector<>();
               
               while(result.next()) {
                   log_TPersonal log_tpersonal = new log_TPersonal(result);
                   vector.add(log_tpersonal);
               }
               
               log_TPersonal[] log_tpersonal = new log_TPersonal[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_tpersonal[i] = vector.elementAt(i);                    
               }
               return log_tpersonal;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TPersonal[] find_log_TPersonal(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tpersonal_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_TPersonal> vector = new Vector<>();
               
               while(result.next()) {
                   log_TPersonal log_tpersonal = new log_TPersonal(result);
                   vector.add(log_tpersonal);
               }
               
               log_TPersonal[] log_tpersonal = new log_TPersonal[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_tpersonal[i] = vector.elementAt(i);                    
               }
               
               return log_tpersonal;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_tpersonal
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_TPersonal(int operacion, log_TPersonal log_tpersonal) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_log_tpersonal(?, ?)}");
                    mensj = "Nuevo Tipo Personal:";
                    break;
                case 2:
                    sql.append("{call sp_upd_log_tpersonal_basic(?, ?, ?)}");
                    mensj = "Actualizando Tipo Personal:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , log_tpersonal.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_tpersonal.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@grp_supplier"    , log_tpersonal.getIdTPersonal());
                    cstmt.setString("@nombre"       , log_tpersonal.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_tpersonal.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_tpersonal.getIdTPersonal()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param tpersonalname valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_TPersonal(String tpersonalname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tpersonal_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@log_tpersonal", tpersonalname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param log_tpersonal
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_log_TPersonal(log_TPersonal log_tpersonal) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_log_tpersonal_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_tpersonal", log_tpersonal.getIdTPersonal());
            int value = 0;
            if(log_tpersonal.getStatus()== 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_tpersonal.getIdTPersonal()+ "","deletelog_tpersonal:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }


    /***************************************************************************/
    /****************************** LOG_VEHICULO *******************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_Vehiculos[] load_log_Vehiculos() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_vehiculo_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<log_Vehiculos> vector = new Vector<>();
               
               while(result.next()) {                   
                   log_Vehiculos log_vehiculos = new log_Vehiculos(result);
                   vector.add(log_vehiculos);
               }
               
               log_Vehiculos[] log_vehiculos = new log_Vehiculos[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_vehiculos[i] = vector.elementAt(i);                    
               }
               return log_vehiculos;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_Vehiculos[] find_log_Vehiculos(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_vehiculo_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_Vehiculos> vector = new Vector<>();
               
               while(result.next()) {
                   log_Vehiculos log_vehiculos = new log_Vehiculos(result);
                   vector.add(log_vehiculos);
               }
               
               log_Vehiculos[] log_vehiculos = new log_Vehiculos[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_vehiculos[i] = vector.elementAt(i);                    
               }
               
               return log_vehiculos;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_vehiculos
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_Vehiculos(int operacion, log_Vehiculos log_vehiculos) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_log_vehiculo_data(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Nuevo Vehiculo:";
                    break;
                case 2:
                    sql.append("{call sp_upd_log_vehiculo_basic(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                    mensj = "Actualizando Vehiculo:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            cstmt.setString("@nro_placa"           , log_vehiculos.getIdPlaca().toUpperCase());
            cstmt.setInt("@tmarca"                 , log_vehiculos.getTmarca().getIdTMarca());
            cstmt.setInt("@tproced"                , log_vehiculos.getTproced().getIdTProced());
            cstmt.setInt("@ttransp"                , log_vehiculos.getTtransp().getIdTTransp());
            cstmt.setString("@modelo"              , log_vehiculos.getModelo().toUpperCase());
            cstmt.setInt("@capacidad"              , log_vehiculos.getCapacidad());
            cstmt.setInt("@ano"                    , log_vehiculos.getAno());
            cstmt.setInt("@clasificacion"          , log_vehiculos.getClasif());
            if (log_vehiculos.getEmpresa() != null)
                cstmt.setString("@empresa"             , log_vehiculos.getEmpresa().toUpperCase());
            else
                cstmt.setString("@empresa"             , log_vehiculos.getEmpresa());
            cstmt.setString("@telefonos"           , log_vehiculos.getTelefonos());
            cstmt.setString("@celular"             , log_vehiculos.getCelular());
            if (log_vehiculos.getCorreo() != null)
                cstmt.setString("@correo"          , log_vehiculos.getCorreo().toLowerCase());
            else
                cstmt.setString("@correo"          , log_vehiculos.getCorreo());

            if (log_vehiculos.getRuta_cc() != null)
                cstmt.setString("@ruta_cc"          , log_vehiculos.getRuta_cc().toLowerCase());
            else
                cstmt.setString("@ruta_cc"          , log_vehiculos.getRuta_cc());
                
            if (log_vehiculos.getRuta_tt() != null)
                cstmt.setString("@ruta_tt"          , log_vehiculos.getRuta_tt().toLowerCase());
            else
                cstmt.setString("@ruta_tt"          , log_vehiculos.getRuta_tt());
                
            if (log_vehiculos.getRuta_rcv() != null)
                cstmt.setString("@ruta_rcv"          , log_vehiculos.getRuta_rcv().toLowerCase());
            else
                cstmt.setString("@ruta_rcv"          , log_vehiculos.getRuta_rcv());
                
            if (log_vehiculos.getRuta_ps() != null)
                cstmt.setString("@ruta_ps"          , log_vehiculos.getRuta_ps().toLowerCase());
            else
                cstmt.setString("@ruta_ps"          , log_vehiculos.getRuta_ps());

            if (log_vehiculos.getRuta_rgt() != null)
                cstmt.setString("@ruta_rgt"          , log_vehiculos.getRuta_rgt().toLowerCase());
            else
                cstmt.setString("@ruta_rgt"          , log_vehiculos.getRuta_rgt());
            
            if(log_vehiculos.getFec_rcv().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_rcv"         , null);
            else
                cstmt.setDate("@fec_rcv"         , log_vehiculos.getFec_rcv());

            if(log_vehiculos.getFec_ps().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_ps"         , null);
            else
                cstmt.setDate("@fec_ps"         , log_vehiculos.getFec_ps());

            if(log_vehiculos.getFec_rgt().toString().equals(LocalDate.now().toString())) 
                cstmt.setDate("@fec_rgt"         , null);
            else
                cstmt.setDate("@fec_rgt"         , log_vehiculos.getFec_rgt());

            cstmt.setString("@nro_rgt"             , log_vehiculos.getNro_rgt());
                
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_vehiculos.getIdPlaca()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el measurename generado ya se encuentra asignado a una medida en la BD
     * @param NroPlaca valor a ser chequeado en la BD
     * @return true si el measure esta en uso, false si esta disponible la medida
     * @throws java.sql.SQLException
     */
    public boolean check_log_Vehiculos(String NroPlaca) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_vehiculo_check(?,?)}");
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@log_vehiculo", NroPlaca);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param log_vehiculos
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_log_Vehiculos(log_Vehiculos log_vehiculos) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_log_vehiculo_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setString("@nro_placa", log_vehiculos.getIdPlaca());
            int value = 0;
            if(log_vehiculos.getStatus() == 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_vehiculos.getIdPlaca()+ "","deletelog_vehiculos:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    
   
    /***************************************************************************/
    /****************************** log_TMarca *********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TMarca[] load_log_TMarca() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tmarca_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<log_TMarca> vector = new Vector<>();
               
               while(result.next()) {
                   log_TMarca log_tmarca = new log_TMarca(result);
                   vector.add(log_tmarca);
               }
               
               log_TMarca[] log_tmarca = new log_TMarca[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_tmarca[i] = vector.elementAt(i);                    
               }
               return log_tmarca;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TMarca[] find_log_TMarca(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tmarca_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_TMarca> vector = new Vector<>();
               
               while(result.next()) {
                   log_TMarca log_tmarca = new log_TMarca(result);
                   vector.add(log_tmarca);
               }
               
               log_TMarca[] log_tmarca = new log_TMarca[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_tmarca[i] = vector.elementAt(i);                    
               }
               
               return log_tmarca;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_tmarca
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_TMarca(int operacion, log_TMarca log_tmarca) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_log_tmarca(?, ?)}");
                    mensj = "Nuevo Tipo Marca:";
                    break;
                case 2:
                    sql.append("{call sp_upd_log_tmarca_basic(?, ?, ?)}");
                    mensj = "Actualizando Tipo Marca:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , log_tmarca.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_tmarca.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@id_tmarca"       , log_tmarca.getIdTMarca());
                    cstmt.setString("@nombre"       , log_tmarca.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_tmarca.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_tmarca.getIdTMarca()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param tmarcaname valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_TMarca(String tmarcaname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tmarca_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@log_tmarca", tmarcaname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param log_tmarca
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_log_TMarca(log_TMarca log_tmarca) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_log_tmarca_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_tmarca", log_tmarca.getIdTMarca());
            int value = 0;
            if(log_tmarca.getStatus()== 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_tmarca.getIdTMarca()+ "","deletelog_tmarca:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }


    /***************************************************************************/
    /****************************** log_TProced ********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TProced[] load_log_TProced() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tproced_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<log_TProced> vector = new Vector<>();
               
               while(result.next()) {
                   log_TProced log_tproced = new log_TProced(result);
                   vector.add(log_tproced);
               }
               
               log_TProced[] log_tproced = new log_TProced[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_tproced[i] = vector.elementAt(i);                    
               }
               return log_tproced;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TProced[] find_log_TProced(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tproced_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_TProced> vector = new Vector<>();
               
               while(result.next()) {
                   log_TProced log_tproced = new log_TProced(result);
                   vector.add(log_tproced);
               }
               
               log_TProced[] log_tproced = new log_TProced[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_tproced[i] = vector.elementAt(i);                    
               }
               
               return log_tproced;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_tproced
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_TProced(int operacion, log_TProced log_tproced) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_log_tproced(?, ?)}");
                    mensj = "Nuevo Tipo Proced:";
                    break;
                case 2:
                    sql.append("{call sp_upd_log_tproced_basic(?, ?, ?)}");
                    mensj = "Actualizando Tipo Proced:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , log_tproced.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_tproced.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@id_tproced"       , log_tproced.getIdTProced());
                    cstmt.setString("@nombre"       , log_tproced.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_tproced.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_tproced.getIdTProced()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param tprocedname valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_TProced(String tprocedname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_tproced_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@log_tproced", tprocedname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param log_tproced
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_log_TProced(log_TProced log_tproced) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_log_tproced_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_tproced", log_tproced.getIdTProced());
            int value = 0;
            if(log_tproced.getStatus()== 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_tproced.getIdTProced()+ "","deletelog_tproced:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }


    /***************************************************************************/
    /****************************** log_TTransp ********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TTransp[] load_log_TTransp() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_ttransp_all}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<log_TTransp> vector = new Vector<>();
               
               while(result.next()) {
                   log_TTransp log_ttransp = new log_TTransp(result);
                   vector.add(log_ttransp);
               }
               
               log_TTransp[] log_ttransp = new log_TTransp[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_ttransp[i] = vector.elementAt(i);                    
               }
               return log_ttransp;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_TTransp[] find_log_TTransp(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_ttransp_find(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_TTransp> vector = new Vector<>();
               
               while(result.next()) {
                   log_TTransp log_ttransp = new log_TTransp(result);
                   vector.add(log_ttransp);
               }
               
               log_TTransp[] log_ttransp = new log_TTransp[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_ttransp[i] = vector.elementAt(i);                    
               }
               
               return log_ttransp;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_ttransp
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_TTransp(int operacion, log_TTransp log_ttransp) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            StringBuilder sql = new StringBuilder();            
            String mensj = "";
            
            switch(operacion){
                case 1:
                    sql.append("{call sp_ins_log_ttransp(?, ?)}");
                    mensj = "Nuevo Tipo Transp:";
                    break;
                case 2:
                    sql.append("{call sp_upd_log_ttransp_basic(?, ?, ?)}");
                    mensj = "Actualizando Tipo Transp:";
                    break;
            }
            CallableStatement cstmt = connection.prepareCall(sql.toString());
            switch(operacion){
                case 1:
                    cstmt.setString("@nombre"       , log_ttransp.getNombre().toUpperCase());
                    cstmt.setString("@abrev"        , log_ttransp.getAbrev().toUpperCase());
                    break;
                case 2:
                    cstmt.setInt("@id_ttransp"       , log_ttransp.getIdTTransp());
                    cstmt.setString("@nombre"        , log_ttransp.getNombre().toUpperCase());
                    cstmt.setString("@abrev"         , log_ttransp.getAbrev().toUpperCase());
                    break;
            }
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_ttransp.getIdTTransp()+ "",mensj);
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param ttranspname valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_TTransp(String ttranspname) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_ttransp_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@log_ttransp", ttranspname);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param log_ttransp
     * @return 
     * @throws java.lang.Exception 
     */
    public boolean change_log_TTransp(log_TTransp log_ttransp) throws Exception {
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        
        try{
         if (connection != null){
            //Inicia Transaccion 
            connection.setAutoCommit(false);
            
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_upd_log_ttransp_status(?,?)}");
            CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
            cstmt.setInt("@id_tproced", log_ttransp.getIdTTransp());
            int value = 0;
            if(log_ttransp.getStatus()== 0){
                value = 1;
            }            
            cstmt.setInt("@status", value );
            cstmt.execute();            
            // Auditar el proceso
            auditar(log_ttransp.getIdTTransp()+ "","deletelog_ttransp:");
            
            connection.commit();
            
            return true;
         }else{
            System.out.println("Error: Connexion no activa");
            throw new Exception("Error de Conexion con la BD");
         }
        }catch(Exception e){            
            connection.rollback();            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }


    /***************************************************************************/
    /********************************* GUIDE ***********************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @param find
     * @param producto
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguid[] find_Archguid(String find, String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguid_find(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numguia"   , find );
               cstmt.setString("@producto"  , producto );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguid> vector = new Vector<>();
               
               while(result.next()) {
                   Archguid archguid = new Archguid(result);
                   vector.add(archguid);
               }
               
               Archguid[] archguid = new Archguid[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguid[i] = vector.elementAt(i);                    
               }
               
               return archguid;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguid_gfc[] find_Archguid_gfc(String find) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguid_find_gfc(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numguia"   , find );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguid_gfc> vector = new Vector<>();
               
               while(result.next()) {
                   Archguid_gfc archguid_gfc = new Archguid_gfc(result);
                   vector.add(archguid_gfc);
               }
               
               Archguid_gfc[] archguid_gfc = new Archguid_gfc[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguid_gfc[i] = vector.elementAt(i);                    
               }
               
               return archguid_gfc;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find
     * @param producto
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguid_cp[] find_Archguid_cp(Integer find, String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguid_find_cp(?, ?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setInt("@numclie"      , find );
               cstmt.setString("@producto"  , producto );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguid_cp> vector = new Vector<>();
               
               while(result.next()) {
                   Archguid_cp archguid_cp = new Archguid_cp(result);
                   vector.add(archguid_cp);
               }
               
               Archguid_cp[] archguid_cp = new Archguid_cp[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguid_cp[i] = vector.elementAt(i);                    
               }
               
               return archguid_cp;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param cliente
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguid_cli[] find_Archguid_cli(String cliente) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguid_find_clie(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , cliente );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguid_cli> vector = new Vector<>();
               
               while(result.next()) {
                   Archguid_cli archguid_clie = new Archguid_cli(result);
                   vector.add(archguid_clie);
               }
               
               Archguid_cli[] archguid_clie = new Archguid_cli[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguid_clie[i] = vector.elementAt(i);                    
               }
               
               return archguid_clie;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param guia
     * @param alm
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguip_det[] find_Archguip_det(String guia, String alm) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_find_det(?, ?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numguia"  , guia );
               cstmt.setString("@almacen"  , alm );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguip_det> vector = new Vector<>();
               
               while(result.next()) {
                   Archguip_det archguip = new Archguip_det(result);
                   vector.add(archguip);
               }
               
               Archguip_det[] archguip = new Archguip_det[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguip[i] = vector.elementAt(i);                    
               }
               
               return archguip;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param producto
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguip_det[] find_Archguip_pro_det(String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_pro_find_det(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find"  , producto );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguip_det> vector = new Vector<>();
               
               while(result.next()) {
                   Archguip_det archguip = new Archguip_det(result);
                   vector.add(archguip);
               }
               
               Archguip_det[] archguip = new Archguip_det[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguip[i] = vector.elementAt(i);                    
               }
               
               return archguip;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param guia
     * @param producto
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguip_pro_cg[] find_Archguip_pro_guia(String guia, String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_find_pro_guia(?, ?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numguia"  , guia );
               cstmt.setString("@producto"  , producto );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguip_pro_cg> vector = new Vector<>();
               
               while(result.next()) {
                   Archguip_pro_cg archguip = new Archguip_pro_cg(result);
                   vector.add(archguip);
               }
               
               Archguip_pro_cg[] archguip = new Archguip_pro_cg[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguip[i] = vector.elementAt(i);                    
               }
               
               return archguip;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param fact
     * @param producto
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguip_pro_dv[] find_Archguip_pro_fact(String fact, String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_find_pro_fact(?, ?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numdoc"  , fact );
               cstmt.setString("@producto"  , producto );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguip_pro_dv> vector = new Vector<>();
               
               while(result.next()) {
                   Archguip_pro_dv archguip = new Archguip_pro_dv(result);
                   vector.add(archguip);
               }
               
               Archguip_pro_dv[] archguip = new Archguip_pro_dv[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguip[i] = vector.elementAt(i);                    
               }
               
               return archguip;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param guia
     * @param alm
     * @return 
     * @throws java.sql.SQLException 
     */
    public Archguip_sum[] find_Archguip_sum(String guia, String alm) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_find_sum(?, ?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numguia"  , guia );
               cstmt.setString("@almacen"  , alm );
               ResultSet result = cstmt.executeQuery();            
               Vector<Archguip_sum> vector = new Vector<>();
               
               while(result.next()) {
                   Archguip_sum archguip = new Archguip_sum(result);
                   vector.add(archguip);
               }
               
               Archguip_sum[] archguip = new Archguip_sum[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   archguip[i] = vector.elementAt(i);                    
               }
               
               return archguip;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param guia valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_Archguie(String guia) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguie_check(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numguia", guia);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param guia
     * @param producto valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_Archguip_guia(String guia, String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_check_guia(?,?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numguia", guia);
               cstmt.setString("@producto", producto);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param fact
     * @param producto valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_Archguip_fact(String fact, String producto) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguip_check_fact(?,?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numdoc", fact);
               cstmt.setString("@producto", producto);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param numdoc valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_Archguid_Numdoc(String numdoc, String guia) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_archguid_check_fact(?,?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numdoc", numdoc);
               cstmt.setString("@numguia", guia);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param guia valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_CGuias_rela(String guia) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_cguias_check_nr(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numrela", guia);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param guia valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_CGuias_carga(String guia) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_cguias_check_ng(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numguia", guia);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * Valida si el groupsuppliername generado ya se encuentra asignado a un grupo en la BD
     * @param guia valor a ser chequeado en la BD
     * @return true si el groupsupplier esta en uso, false si esta disponible el grupo
     * @throws java.sql.SQLException
     */
    public boolean check_log_CGuias_fcarga(String guia) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_cguias_check_fcar(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
               cstmt.setString("@numfcar", guia);
               cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
               cstmt.execute();
               if(cstmt.getInt("result") == 1) return true;               
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return false;
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param log_cguias
     * @param pos
     * @return
     * @throws SQLException
     * @throws Exception 
     */
    public boolean save_log_CGuias(
            log_CGuias log_cguias, int operacion, int pos) throws SQLException, Exception {
        
        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();        
        try{
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql_rela = new StringBuilder();            
                StringBuilder sql_data = new StringBuilder();            
                StringBuilder sql_chof = new StringBuilder();            
                StringBuilder sql_vehi = new StringBuilder();            
                StringBuilder sql_ayud = new StringBuilder();            
                StringBuilder sql_cheq = new StringBuilder();            
                StringBuilder sql_perm = new StringBuilder();            
                String mensj = "";

                switch(operacion){
                    case 1:
                        sql_rela.append("{call sp_ins_log_cguias_numrela(?)}");
                        sql_data.append("{call sp_ins_log_cguias_data(?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        sql_chof.append("{call sp_ins_log_cguias_chof(?, ?, ?)}");
                        sql_vehi.append("{call sp_ins_log_cguias_vehi(?, ?, ?, ?)}");
                        sql_ayud.append("{call sp_ins_log_cguias_ayud(?, ?, ?, ?)}");
                        sql_cheq.append("{call sp_ins_log_cguias_cheq(?, ?, ?, ?)}");
                        sql_perm.append("{call sp_ins_log_cguias_cheq(?, ?, ?, ?)}");
                        mensj = "Nueva log_CGuias:";
                        break;
                    case 2:
                        log_cguias.setNumrela(String.valueOf(Datos.getNumRela()));

                        sql_data.append("{call sp_upd_log_cguias_data(?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        sql_chof.append("{call sp_upd_log_cguias_chof(?, ?, ?)}");
                        sql_vehi.append("{call sp_upd_log_cguias_vehi(?, ?, ?, ?)}");
                        sql_ayud.append("{call sp_upd_log_cguias_ayud(?, ?, ?, ?)}");
                        sql_cheq.append("{call sp_upd_log_cguias_cheq(?, ?, ?, ?)}");
                        sql_perm.append("{call sp_upd_log_cguias_cheq(?, ?, ?, ?)}");
                        mensj = "Actualizando log_CGuias:";
                        break;
                }
                CallableStatement cstmt = null;
                if (pos == 0){
                    /**
                     * Busqueda NumRela
                     */
                    if (operacion == 1){
                        cstmt = connection.prepareCall(sql_rela.toString());
                        cstmt.registerOutParameter("numrela"    , java.sql.Types.VARCHAR);
                        cstmt.execute();            
                        log_cguias.setNumrela(cstmt.getString("numrela"));
                        Datos.setNumRela(cstmt.getString("numrela"));
                    }


                    /**
                     * Choferes
                     */
                    cstmt = connection.prepareCall(sql_chof.toString());
                    cstmt.setString("@numrela"         , log_cguias.getNumrela());
                    cstmt.setDate("@fecha"             , log_cguias.getFecha());
                    cstmt.setString("@id_personal"     , log_cguias.getChofer());
                    cstmt.execute();            


                    /**
                     * Vehiculos
                     */
                    cstmt = connection.prepareCall(sql_vehi.toString());
                    cstmt.setString("@numrela"         , log_cguias.getNumrela());
                    cstmt.setDate("@fecha"             , log_cguias.getFecha());
                    cstmt.setString("@nro_placa1"      , log_cguias.getVeh1());
                    cstmt.setString("@nro_placa2"      , log_cguias.getVeh2());
                    cstmt.execute();            


                    /**
                     * Ayudantes
                     */
                    cstmt = connection.prepareCall(sql_ayud.toString());
                    cstmt.setString("@numrela"         , log_cguias.getNumrela());
                    cstmt.setDate("@fecha"             , log_cguias.getFecha());
                    if(log_cguias.getAyud1().equals("")) 
                        cstmt.setString("@id_personal1", null);
                    else
                        cstmt.setString("@id_personal1", log_cguias.getAyud1());

                    cstmt.setString("@id_personal2"    , log_cguias.getAyud2());
                    cstmt.execute();            


                    /**
                     * Chequeador Puerta
                     */
                    cstmt = connection.prepareCall(sql_cheq.toString());
                    cstmt.setString("@numrela"         , log_cguias.getNumrela());
                    cstmt.setDate("@fecha"             , log_cguias.getFecha());
                    cstmt.setString("@id_personal"     , log_cguias.getCheq_pta());
                    cstmt.setInt("@id_tchequeo"        , log_cguias.getTcheq_pta());
                    cstmt.execute();            

                    /**
                     * Sada e Insopesca
                     */

                }


                /**
                 * Guias de Carga
                 */
                cstmt = connection.prepareCall(sql_data.toString());
                cstmt.setString("@numrela"              , log_cguias.getNumrela());
                cstmt.setDouble("@numorden"             , pos + 1);
                cstmt.setString("@numguia"              , log_cguias.getNumguia());
                cstmt.setInt("@numpuerta"               , log_cguias.getNumpuerta());
                cstmt.setInt("@numfacturas"             , log_cguias.getNumfact());
                cstmt.setInt("@numclientes"             , log_cguias.getNumclie());
                cstmt.setString("@status"               , log_cguias.getStat_guia());
                cstmt.setInt("@anulada"                 , log_cguias.getAnulada());
                cstmt.setDate("@fecha"                  , log_cguias.getFecha());
                cstmt.execute();            

                // Auditar el proceso
                auditar(log_cguias.getNumrela()+ "",mensj);

                connection.commit();

                return true;
            }else{
                System.out.println("Error: Connexion no activa");
                throw new Exception("Error de Conexion con la BD");
            }
        }catch(Exception e){            
            connection.rollback();   
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e);    
            throw e;
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2
     * UPDATE
     * @param log_cguias_perm
     * @param pos
     * @return
     * @throws SQLException
     * @throws Exception
     */
    public boolean save_log_CGuias_perm(
            log_CGuias_perm log_cguias_perm, int operacion, int pos) throws SQLException, Exception {

        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        try {
            if (connection != null) {
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sqlProc = new StringBuilder();
                String mensj = "";

                switch (operacion) {
                    case 1:
                        sqlProc.append("{call sp_ins_log_cguias_perm(?, ?, ?, ?, ?)}");
                        mensj = "Nueva log_CGuias_perm:";
                        break;
                    case 2:
                        sqlProc.append("{call sp_upd_log_personal_basic(?, ?, ?, ?)}");
                        mensj = "Actualizando log_CGuias_perm:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString("@numrela", log_cguias_perm.getNumrela());
                cstmt.setDouble("@numorden", pos + 1);
                cstmt.setString("@numpermiso", log_cguias_perm.getGuias());
                cstmt.setInt("@id_tpermiso", log_cguias_perm.getTpermiso());
                cstmt.setDate("@fecha", log_cguias_perm.getFecha());
                cstmt.execute();

                // Auditar el proceso
                auditar(log_cguias_perm.getNumrela() + "", mensj);

                connection.commit();

                return true;
            } else {
                System.out.println("Error: Connexion no activa");
                throw new Exception("Error de Conexion con la BD");
            }
        } catch (Exception e) {
            connection.rollback();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace, "Error = " + e);
            throw e;
        } finally {
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2
     * UPDATE
     * @param log_cguias_falt
     * @param pos
     * @return
     * @throws SQLException
     * @throws Exception
     */
    public boolean save_log_CGuias_falt(
            log_CGuias_falt_cg log_cguias_falt, int operacion, int pos) throws SQLException, Exception {

        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        try {
            if (connection != null) {
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sqlProc = new StringBuilder();
                String mensj = "";

                switch (operacion) {
                    case 1:
                        sqlProc.append("{call sp_ins_log_cguias_falt(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nueva log_CGuias_falt:";
                        break;
                    case 2:
                        sqlProc.append("{call sp_upd_log_cguias_falt(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Actualizando log_CGuias_falt:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString("@numrela", log_cguias_falt.getNumrela());
                cstmt.setDouble("@numorden", pos + 1);
                cstmt.setString("@numguia", log_cguias_falt.getNumguiac());
                cstmt.setString("@numfalt", log_cguias_falt.getNumguiaf());
                cstmt.setString("@producto", log_cguias_falt.getProducto());
                cstmt.setInt("@cantfact", log_cguias_falt.getCantfact());
                cstmt.setInt("@cantfalt", log_cguias_falt.getCantfalt());
                cstmt.setInt("@cantdesp", log_cguias_falt.getCantdesp());
                cstmt.setInt("@id_unidad", log_cguias_falt.getId_unidad());
                cstmt.setDate("@fecha", log_cguias_falt.getFecha());
                cstmt.execute();

                // Auditar el proceso
                auditar(log_cguias_falt.getNumrela() + "", mensj);

                connection.commit();

                return true;
            } else {
                System.out.println("Error: Connexion no activa");
                throw new Exception("Error de Conexion con la BD");
            }
        } catch (Exception e) {
            connection.rollback();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace, "Error = " + e);
            throw e;
        } finally {
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2
     * UPDATE
     * @param log_cguias_dev
     * @param pos
     * @return
     * @throws SQLException
     * @throws Exception
     */
    public boolean save_log_CGuias_dev(
            log_CGuias_falt_dv log_cguias_dev, int operacion, int pos) throws SQLException, Exception {

        Bd bd = ConnBdType.open(ConnBdType.SqlServer);
        connection = bd.open();
        try {
            if (connection != null) {
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql_rela = new StringBuilder();            
                StringBuilder sqlProc = new StringBuilder();
                String mensj = "";

                switch (operacion) {
                    case 1:
                        sql_rela.append("{call sp_ins_log_cguias_numreladev(?)}");
                        sqlProc.append("{call sp_ins_log_cguias_dev(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nueva log_CGuias_dev:";
                        break;
                    case 2:
                        sqlProc.append("{call sp_upd_log_personal_basic(?, ?, ?, ?)}");
                        mensj = "Actualizando log_CGuias_dev:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                if (pos == 0){
                    /**
                     * Busqueda NumRelaDev
                     */
                    if (operacion == 1){
                        cstmt = connection.prepareCall(sql_rela.toString());
                        cstmt.registerOutParameter("numrela"    , java.sql.Types.DECIMAL);
                        cstmt.execute();            
                        log_cguias_dev.setNumdev(cstmt.getString("numrela"));
                        Datos.setNumRelDev(cstmt.getString("numrela"));
                    }
                }
                cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString("@numrela", log_cguias_dev.getNumrela());
                cstmt.setDouble("@numorden", pos + 1);
                cstmt.setString("@numdev", log_cguias_dev.getNumdev());
                cstmt.setString("@numdoc", log_cguias_dev.getNumdoc());
                cstmt.setString("@id_personal", log_cguias_dev.getCheq_pta());
                cstmt.setString("@producto", log_cguias_dev.getProducto());
                cstmt.setInt("@id_motivo", log_cguias_dev.getId_motivo());
                cstmt.setInt("@cantfact", log_cguias_dev.getCantfact());
                cstmt.setInt("@cantdev", log_cguias_dev.getCantdev());
                cstmt.setInt("@cantdesp", log_cguias_dev.getCantdesp());
                cstmt.setInt("@id_unidad", log_cguias_dev.getId_unidad());
                cstmt.setString("@observ", log_cguias_dev.getObserv());
                cstmt.setDate("@fecha", log_cguias_dev.getFecha());
                cstmt.execute();

                // Auditar el proceso
                auditar(log_cguias_dev.getNumrela()+ "", mensj);

                connection.commit();

                return true;
            } else {
                System.out.println("Error: Connexion no activa");
                throw new Exception("Error de Conexion con la BD");
            }
        } catch (Exception e) {
            connection.rollback();
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace, "Error = " + e);
            throw e;
        } finally {
            connection.close();
        }
    }
    /**
     * @author MITM
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_CGuias[] load_log_CGuias() throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call xxx}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               ResultSet result = cstmt.executeQuery();            
               
               Vector<log_CGuias> vector = new Vector<>();
               
               while(result.next()) {
                   log_CGuias log_cguias = new log_CGuias(result);
                   vector.add(log_cguias);
               }
               
               log_CGuias[] log_cguias = new log_CGuias[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_cguias[i] = vector.elementAt(i);                    
               }
               return log_cguias;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param find1
     * @param find2
     * @param param
     * @param rows
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_CGuias[] find_log_CGuias(String find1, String find2, String param, int rows) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            if (param.equals(""))
                sqlProc.append("{call sp_get_log_cguias_find(?, ?, ?)}");
            if (param.equals("ng"))
                sqlProc.append("{call sp_get_log_cguias_find_ng(?, ?, ?)}");
            if (param.equals("nf"))
                sqlProc.append("{call sp_get_log_cguias_find_nf(?, ?, ?)}");
            if (param.equals("nc"))
                sqlProc.append("{call sp_get_log_cguias_find_nc(?, ?, ?)}");
            if (param.equals("cp"))
                sqlProc.append("{call sp_get_log_cguias_find_prod(?, ?, ?)}");
            if (param.equals("vh"))
                sqlProc.append("{call sp_get_log_cguias_find_vehi(?, ?, ?)}");
            if (param.equals("cf"))
                sqlProc.append("{call sp_get_log_cguias_find_chof(?, ?, ?)}");
            if (param.equals("ay"))
                sqlProc.append("{call sp_get_log_cguias_find_ayud(?, ?, ?)}");
            if (param.equals("cq"))
                sqlProc.append("{call sp_get_log_cguias_find_cheq(?, ?, ?)}");
            
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@find1"  , find1 );
               cstmt.setString("@find2"  , find2 );
               cstmt.setInt("@rows"  , rows );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_CGuias> vector = new Vector<>();
               
               while(result.next()) {
                   log_CGuias log_cguias = new log_CGuias(result);
                   vector.add(log_cguias);
               }
               
               log_CGuias[] log_cguias = new log_CGuias[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_cguias[i] = vector.elementAt(i);                    
               }
               
               return log_cguias;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param numrela
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_CGuias_perm[] find_log_CGuias_perm(String numrela) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_cguias_find_perm(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numrela"  , numrela );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_CGuias_perm> vector = new Vector<>();
               
               while(result.next()) {
                   log_CGuias_perm log_cguias_perm = new log_CGuias_perm(result);
                   vector.add(log_cguias_perm);
               }
               
               log_CGuias_perm[] log_cguias_perm = new log_CGuias_perm[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_cguias_perm[i] = vector.elementAt(i);                    
               }
               
               return log_cguias_perm;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param numrela
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_CGuias_falt_cg[] find_log_CGuias_fcar(String numrela) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_cguias_find_fcar(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numrela"  , numrela );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_CGuias_falt_cg> vector = new Vector<>();
               
               while(result.next()) {
                   log_CGuias_falt_cg log_cguias_fcar = new log_CGuias_falt_cg(result);
                   vector.add(log_cguias_fcar);
               }
               
               log_CGuias_falt_cg[] log_cguias_fcar = new log_CGuias_falt_cg[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_cguias_fcar[i] = vector.elementAt(i);                    
               }
               
               return log_cguias_fcar;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    /**
     * @author MITM
     * @param numrela
     * @return 
     * @throws java.sql.SQLException 
     */
    public log_CGuias_falt_dv[] find_log_CGuias_fdev(String numrela) throws SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_log_cguias_find_fdev(?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setString("@numrela"  , numrela );
               ResultSet result = cstmt.executeQuery();            
               Vector<log_CGuias_falt_dv> vector = new Vector<>();
               
               while(result.next()) {
                   log_CGuias_falt_dv log_cguias_fdev = new log_CGuias_falt_dv(result);
                   vector.add(log_cguias_fdev);
               }
               
               log_CGuias_falt_dv[] log_cguias_fdev = new log_CGuias_falt_dv[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   log_cguias_fdev[i] = vector.elementAt(i);                    
               }
               
               return log_cguias_fdev;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }

    
    /***************************************************************************/
    /****************************** PRINT SCREEN *******************************/
    /***************************************************************************/
    
    /**
     * @author MITM
     * @param idscreen
     * @param idobjeto
     * @return 
     * @throws java.sql.SQLException 
     */
    public ItemPrintScreen[] find_PrintScreen(int idscreen, String idobjeto) throws SQLException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
        
            StringBuilder sqlProc = new StringBuilder();
            sqlProc.append("{call sp_get_menu_print(?,?)}");
            
            if (connection != null){
               CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
               cstmt.setInt("@idscreen"  , idscreen );
               cstmt.setString("@idobjeto"  , idobjeto );
               ResultSet result = cstmt.executeQuery();            
               
               Vector<ItemPrintScreen> vector = new Vector<>();
               
               while(result.next()) {
                   ItemPrintScreen printscreen = new ItemPrintScreen(result);
                   vector.add(printscreen);
               }
               
               ItemPrintScreen[] printscreen = new ItemPrintScreen[vector.size()];
               for (int i = 0; i < vector.size(); i++) {
                   printscreen[i] = vector.elementAt(i);                    
               }
               
               return printscreen;
            }else{
                System.out.println("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            StackTraceElement[] stacktrace = Thread.currentThread().getStackTrace();
            Tools.getErrorMessage(stacktrace,"Error = " + e); 
        }finally{
            connection.close();
        }
        return null;
    }
    
    
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/
    
    private static class BdHolder {
        private static final Bd INSTANCE = new Bd();
    }
    
    public static Bd getInstance() {
        return Bd.BdHolder.INSTANCE;
    }
}

